package com.yincheng.oes.business.util;

import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

public class ExportExcel {

    // 导出数据
    public void export(OutputStream os) throws Exception {
        wb.write(os);
        os.flush();
    }

    protected HSSFWorkbook wb;
    protected HSSFCellStyle titleCellStyle;
    protected HSSFCellStyle contentCellStyle;

    // 构造函数，传入要导出的数据
    public ExportExcel(String[] rowName, List<List<Object>> dataList) {

        this.wb = new HSSFWorkbook();
        this.titleCellStyle = createTitleCellStyle();
        this.contentCellStyle = createContentStyle();

        HSSFSheet sheet = wb.createSheet("sheet0");
        setTitle(sheet, rowName);
        setContext(sheet, dataList);
        setSheetAutoSizeColumn(0, sheet);
    }

    protected void setTitle(HSSFSheet sheet, String[] titleNames) {
        HSSFRow head = sheet.createRow(0);
        for (int n = 0; n < titleNames.length; n++) {
            HSSFCell cell = head.createCell(n);
            cell.setCellType(CellType.STRING);
            HSSFRichTextString text = new HSSFRichTextString(titleNames[n]);
            cell.setCellValue(text);
            cell.setCellStyle(titleCellStyle);
            cell.setAsActiveCell();

        }
    }

    protected void setContext(HSSFSheet sheet, List<List<Object>> rows) {
        int i = 1;
        for (List<Object> row : rows) {
            HSSFRow frow = sheet.createRow(i);

            int j = 0;
            for (Object data : row) {
                HSSFCell cell = frow.createCell(j);
                cell.setCellStyle(contentCellStyle);
                cell.setAsActiveCell();

                // String fieldName = kv.getKey();
                String value = getFieldValue(data);
                cell.setCellValue(value);
                j++;
            }

            i++;
        }
    }

    private String getFieldValue(Object value) {
        if (value instanceof Date) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            return sdf.format(value);
        }
        return value == null ? "" : value.toString();
    }

    protected void setSheetAutoSizeColumn(int rowIndex, HSSFSheet sheet) {
        int coloumNum = sheet.getRow(rowIndex).getPhysicalNumberOfCells();
        for (int i = 0; i < coloumNum; i++) {
            sheet.autoSizeColumn((short) i); // 调整列宽度
        }
    }

    /**
     * 创建每列的内容的样式
     */
    protected HSSFCellStyle createTitleCellStyle() {
        HSSFCellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(HSSFColorPredefined.GREY_25_PERCENT.getIndex());// 设置填充背景为浅灰色

        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);// 居中
        style.setFont(createTitleFont());// 把字体应用到当前的样式

        return style;
    }

    /**
     * 设置每列的内容的样式
     */
    private HSSFCellStyle createContentStyle() {
        HSSFCellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(HSSFColorPredefined.WHITE.getIndex());
        style.setFont(createContentFont());
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);// 居中
        return style;
    }

    /**
     * 设置每列的内容的文字样式
     */
    protected HSSFFont createContentFont() {
        HSSFFont font = wb.createFont();
        Short st = 11;
        font.setColor(HSSFColorPredefined.BLACK.getIndex());
        font.setFontHeightInPoints(st);
        return font;
    }

    /**
     * 创建每列的内容的文字样式
     */
    protected HSSFFont createTitleFont() {
        HSSFFont font = wb.createFont();
        font.setColor(HSSFColorPredefined.BLACK.getIndex());
        ;// 设置字体为紫色
        font.setBold(true);// 设置字体为加粗
        font.setFontHeightInPoints((short) 12);// 设置字体大小为12号
        return font;
    }
}
